﻿package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import entity.Student;
import utils.JDBCUtil;
//201521123094
public class StudentDaoJDBCImpl implements StudentDao {

	@Override
	public int add(entity.Student stu) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "insert into students(stuno,name,age,birthdate) values(?,?,?,?)";
		int result = -1;
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, stu.getStuno());
			pstat.setString(2, stu.getName());
			pstat.setInt(3, stu.getAge());
			pstat.setString(4, stu.getBirthdate());
			result = pstat.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
		}
		return result > 0 ? 1 : -1;
	}

	@Override
	public int update(entity.Student stu) {
		Scanner in = new Scanner(System.in);
		int age = in.nextInt();
		int id = in.nextInt();
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "update students set age=? where id = ?;";
		int result = -1;
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, age);
			pstat.setInt(2, id);
			result = pstat.executeUpdate();

		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
		}
		in.close();
		return result > 0 ? 1 : -1;
	}

	@SuppressWarnings("null")
	@Override
	public entity.Student findById(String sid) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from students where id=?;";
		Student student = null;
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, sid);
			while(rs.next()){
				int id = rs.getInt("id");
				String stuno = rs.getString("stuno");
				String name = rs.getString("name");
				int age = rs.getInt("age");
				String date = rs.getString("birthdate");
				student = new Student(id, stuno, name, age, date);
				System.out.print("id=" + id + "\tstuno=" + stuno + "\tname=" + name + "\tage=" + age + "\tbirthdate="
						+ date + "\n");
			}
		
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
		}
		return student;
	}

	@Override
	public int delete(int sid) {
		Scanner in = new Scanner(System.in);
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "delete from students where id=?;";
		int result = -1;
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, sid);
			result = pstat.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
			in.close();
		}
		return result > 0 ? 1 : -1;
	}

	@Override
	public List<entity.Student> findAll() {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<entity.Student> students = new ArrayList<Student>();
		String sql = "select * from students;";
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String stuno = rs.getString("stuno");
				String name = rs.getString("name");
				int age = rs.getInt("age");
				String date = rs.getString("birthdate");
				Student stu = new Student(id, stuno, name, age, date);
				students.add(stu);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
		}
		return students;
	}

	@Override
	public List<entity.Student> findByName(String name) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<entity.Student> students = new ArrayList<Student>();
		String sql = "select * from students where name=?;";
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			rs = pstat.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String stuno = rs.getString("stuno");
				name = rs.getString("name");
				int age = rs.getInt("age");
				String date = rs.getString("birthdate");
				Student stu = new Student(id, stuno, name, age, date);
				students.add(stu);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
		}
		return students;
	}

}
